﻿using System;
using System.Collections.Generic;
using System.Linq;
using System.Web;
using System.Data;
using System.Data.OleDb;
using System.Reflection;
using System.Text;
using System.Data.OracleClient;
//using Oracle.DataAccess.Client;

/// <summary>
///AccessHelper 的摘要说明

/// </summary>
/// 
namespace bdnt_pets_system_Bo
{
    public class AccessHelper
    {
        private string strConnection { get; set; }
        private static AccessHelper instance = null;
        public static AccessHelper GetInstance()
        {
            if (instance == null)
                instance = new AccessHelper();
            return instance;
        }

        public AccessHelper()
        {
            strConnection = System.Configuration.ConfigurationManager.ConnectionStrings["DB"].ConnectionString;
        }

        public DataTable ExcuteDataTable(string sql, OracleParameter[] paras = null)
        {
            OracleConnection objConnection = new OracleConnection(strConnection);
            DataTable dt = new DataTable();
            try
            {
                objConnection.Open();
                OracleDataAdapter da = new OracleDataAdapter(sql, objConnection);
                if (paras != null)
                {
                    da.SelectCommand.Parameters.AddRange(paras);
                }
                da.Fill(dt);
                da.Dispose();
                return dt;
            }
            catch (Exception)
            {
                throw;
            }
            finally
            {
                objConnection.Close();
                objConnection.Dispose();
            }
        }

        public DataSet ExcuteDataSet(string sql, OracleParameter[] paras = null)
        {
            OracleConnection objConnection = new OracleConnection(strConnection);
            DataSet ds = new DataSet();
            try
            {
                objConnection.Open();
                OracleDataAdapter da = new OracleDataAdapter(sql, objConnection);
                if (paras != null)
                {
                    da.SelectCommand.Parameters.AddRange(paras);
                }
                da.Fill(ds);
                da.Dispose();
                return ds;
            }
            catch (Exception)
            {
                throw;
            }
            finally
            {
                objConnection.Close();
                objConnection.Dispose();
            }
        }

        public Int32 ExcuteNonQueryDataSet(string sql, OracleParameter[] paras = null)
        {
            Int32 re = 0;
            OracleConnection objConnection = new OracleConnection(strConnection);
            try
            {
                objConnection.Open();
                OracleDataAdapter da = new OracleDataAdapter(sql, objConnection);               
                var co = da.InsertCommand;
                re = co.ExecuteNonQuery(); 
               
                da.Dispose();
                return re;
            }
            catch (Exception)
            {
                return 0;
                throw;
            }
            finally
            {
                objConnection.Close();
                objConnection.Dispose();
            }
           
        }

        public bool ExcultData(string sql, OracleParameter[] param = null)
        {
            OracleConnection objConnection = new OracleConnection(strConnection);
            OracleCommand cmd = new OracleCommand(sql, objConnection);
            try
            {
                if (objConnection.State == ConnectionState.Closed)
                { objConnection.Open(); }
                if (param != null)
                    cmd.Parameters.AddRange(param);
                return cmd.ExecuteNonQuery() > 0 ? true : false;
            }
            catch (OleDbException ob)
            {
                throw ob;
            }
            finally
            {
                cmd.Dispose();
                objConnection.Close();
                objConnection.Dispose();
            }
        }

        public bool ExcultProc(string sql, OracleParameter[] param = null)
        {
            OracleConnection objConnection = new OracleConnection(strConnection);
            OracleCommand cmd = new OracleCommand(sql, objConnection);
            try
            {
                if (objConnection.State == ConnectionState.Closed)
                {
                    objConnection.Open();
                }
                if (param != null)
                    cmd.Parameters.AddRange(param);
                cmd.ExecuteNonQuery();
                return true;
            }
            catch (OleDbException ob)
            {
                return false;
                throw ob;
            }
            finally
            {
                cmd.Dispose();
                objConnection.Close();
                objConnection.Dispose();
            }
        }

        public int ExecuteNonQuery(string sql, OracleParameter[] param = null)
        {
            OracleConnection objConnection = new OracleConnection(strConnection);
            OracleCommand cmd = new OracleCommand(sql, objConnection);
            try
            {
                if (param != null)
                    cmd.Parameters.AddRange(param);
                objConnection.Open();
                return cmd.ExecuteNonQuery();
            }
            catch (OleDbException ob)
            {
                throw ob;
            }
            finally
            {
                cmd.Dispose();
                objConnection.Close();
                objConnection.Dispose();
            }
        }

        public int ExecuteNonQuery(List<string> sqls)
        {
            OracleConnection objConnection = new OracleConnection(strConnection);
            //OracleCommand cmd = new OracleCommand(sql, objConnection);
            OracleCommand cmd = objConnection.CreateCommand();
            try
            {
                int i = 0;
                objConnection.Open();
                foreach (var item in sqls)
                {
                    if (string.IsNullOrEmpty(item))
                        continue;
                    cmd.CommandText = item;
                    i = i + cmd.ExecuteNonQuery();
                }
                return i;
            }
            catch (OleDbException ob)
            {
                throw ob;
            }
            finally
            {
                cmd.Dispose();
                objConnection.Close();
                objConnection.Dispose();
            }
        }

        public object ExecuteScalar(string sql, OracleParameter[] param = null)
        {
            OracleConnection objConnection = new OracleConnection(strConnection);
            OracleCommand cmd = new OracleCommand(sql, objConnection);
            try
            {
                if (param != null)
                    cmd.Parameters.AddRange(param);
                objConnection.Open();
                return cmd.ExecuteScalar();
            }
            catch (OleDbException ob)
            {
                throw ob;
            }
            finally
            {
                cmd.Dispose();
                objConnection.Close();
                objConnection.Dispose();
            }
        }

        public object ExecuteScalar(string sql, string sql2, OracleParameter[] param = null)
        {
            OracleConnection objConnection = new OracleConnection(strConnection);
            OracleCommand cmd = objConnection.CreateCommand();
            try
            {
                cmd.CommandText = sql;
                if (param != null)
                    cmd.Parameters.AddRange(param);
                objConnection.Open();
                var cuo = cmd.ExecuteNonQuery();
                if (cuo > 0)
                {
                    cmd.CommandText = sql2;
                    return cmd.ExecuteScalar();
                }
                else return null;
            }
            catch (OleDbException ob)
            {
                return 0;
                throw ob;
            }
            finally
            {
                cmd.Dispose();
                objConnection.Close();
                objConnection.Dispose();
            }
        }
    }
}
